import pymysql
import socket
hostname = socket.gethostname()

debug_hosts = [ 'JSB']#'spider-test-db-1.istarshine.net.cn', 'ZHXG',
if hostname in debug_hosts:
    task_db_info = {
        'host': '192.168.16.223',
        'port': 3306,
        'user': 'root',
        'password': 'zhxg_140101',
        'db': 'zhongbao',
        'charset': 'utf8mb4',
        'cursorclass': pymysql.cursors.DictCursor
    }
    task_table = "zhongbao_tasks_new"
else:
    task_db_info = {
        'host': '192.168.19.8',
        'port': 3306,
        'user': 'xgzb_user',
        'password': 'lOU0STsYOWlCR9sg',
        'db': 'Domain',
        'charset': 'utf8',
        'cursorclass': pymysql.cursors.DictCursor
    }
    task_table = "project_domain_relation"

class ToObject():
    def __init__(self, args):
        self.items = []
        self.results = []
        self.length = 0
        if type(args) == list:
            for kwarg in args:
                if kwarg:
                    self.length += 1
                    self.items.append(ToObject(kwarg))
                    self.results.append(kwarg)
        elif type(args) == dict:
            if args:
                self.length = 1
                self.results = args
                for key, value in args.items():
                    self.__setattr__(key, value)
        else:
            raise TypeError("参数必须是单独的字典，或者字典的列表。")

    def count(self):
        return self.length

    def extract(self):
        return self.results

    def objects(self):
        return self.items

class Objects():
    def __init__(self, db_info, db_table):
        self.db_info = db_info
        self.db_table = db_table

    def getAll(self):
        return self.all(isAbandon=0)

    def all(self, isAbandon=None):
        conn = pymysql.connect(**(self.db_info))
        cur = conn.cursor()
        tasks = []
        #sql = "select * from {} where project_id > 0 and allocation_flag <> null".format(self.db_table)
        if isAbandon == None:
            sql = "select * from {}".format(self.db_table)
        elif isAbandon == 0:
            sql = "select * from {} where isAbandon=0".format(self.db_table)
        elif isAbandon == 1:
            sql = "select * from {} where isAbandon=1".format(self.db_table)
        else:
            raise ValueError("The params isAbandon must be None, 0 or 1.")
        try:
            cur.execute(sql)
            tasks = list(cur.fetchall())
        except Exception as e:
            pass
        finally:
            conn.close()
        return tasks

    def allocated(self):
        conn = pymysql.connect(**(self.db_info))
        cur = conn.cursor()
        tasks = []
        #sql = "select * from {} where project_id > 0 and allocation_flag <> null".format(self.db_table)
        #sql = "select * from {} where project_id > 0 and allocation_flag is not null".format(self.db_table)
        sql = "select * from {} where isAbandon=0 and status > 1".format(self.db_table)
        try:
            cur.execute(sql)
            tasks = list(cur.fetchall())
        except Exception as e:
            pass
        finally:
            conn.close()
        return tasks

    def unallocated(self):
        conn = pymysql.connect(**(self.db_info))
        cur = conn.cursor()
        tasks = []
        #sql = "select * from {} where project_id > 0 and allocation_flag <> null".format(self.db_table)
        #sql = "select * from {} where project_id > 0 and allocation_flag is null".format(self.db_table)
        sql = "select * from {} where isAbandon=0 and status = 1".format(self.db_table)
        try:
            cur.execute(sql)
            tasks = list(cur.fetchall())
        except Exception as e:
            pass
        finally:
            conn.close()
        return tasks

    def filter(self, **kwargs):
        conn = pymysql.connect(**(self.db_info))
        cur = conn.cursor()
        tasks = []
        #sql = "select * from {} where project_id > 0 and allocation_flag <> null".format(self.db_table)
        sql = "select * from {} where isAbandon=0".format(self.db_table)
        for key, value in kwargs.items():
            if value == None:
                value = 'null'
            if isinstance(value, list) or isinstance(value, tuple):
                sql += " and {} in {}".format(key, tuple(value))
            else:
                sql += " and {} = {}".format(key, value)
        try:
            cur.execute(sql)
            tasks = list(cur.fetchall())
        except Exception as e:
            pass
        finally:
            conn.close()
        return tasks

    def get(self, **kwargs):
        if not kwargs:
            raise ValueError("0 kwargs was given.")
        length = len(kwargs)
        if length > 1:
            raise ValueError("Only need 1 kwargs, but too many were given.")
        tasks = self.filter(**kwargs)
        if len(tasks) > 1:
            raise ValueError("Results not single.")
        elif len(tasks) == 0:
            return None
        else:
            return tasks[0]

    def update(self,id,**kwargs):
        if not kwargs:
            raise ValueError("0 kwargs was given.")
        keyValues = []
        for key, value in kwargs.items():
            if value == None:
                value = "null"
            if isinstance(value, str) and (value != "null"):
                value = "'{}'".format(value)
            keyValues.append("{}={}".format(key,value))
        sql = "update {} set ".format(self.db_table)
        _sql = ",".join(keyValues)
        sql = sql + _sql + " where id={}".format(id)
        conn = pymysql.connect(**(self.db_info))
        cur = conn.cursor()
        try:
            cur.execute(sql)
            conn.commit()
            return True
        except Exception as e:
            return False
        finally:
            conn.close()

class Task():
    objects = Objects(task_db_info, task_table)

